﻿<%@ WebHandler Language="C#" Class="RbContainerShipRecordReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 热表转工报表(生产部考核)
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.25
 */

public class RbContainerShipRecordReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "getCompute":
                        r = getComputeData(jObject);//查询统计数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChart":
                        r = getChart(jObject);//查询柱状图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChartDetail":
                        r = getChartDetail(jObject);//查询柱状图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportPlanRate":
                        exportPlanRate(jObject);//导出排产率明细
                        break;
                    case "exportPlanTemp":
                        exportPlanTemp(jObject);//导出排产临时项
                        break;
                    case "exportNoIntime":
                        exportNoIntime(jObject);//导出未准时到达
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //排产率查询公用sql
    string getComputeDataSql(JObject jObject)
    {
        string sql = @"
SELECT  distinct f.factoryname,cci.fromworkcentername,cci.productname,cci.productdescription,cci.containername,cci.workflowstepname currentspec,
        CASE WHEN csr.recivedate IS NOT NULL THEN (csr.acceptanceqty + csr.lossqty) ELSE cci.qty END qty,
        ws.workflowstepname,
        e.fullname receiver,csd.toacceptdate recivedate,
        CASE WHEN pdr.parentid IS NOT NULL THEN '转工拒收'
            WHEN pdr.parentid IS NULL AND csd.senddate IS NULL THEN '未转出'
            WHEN pdr.parentid IS NULL AND csd.senddate IS NOT NULL AND csd.toacceptdate IS NULL THEN '待接收'  
            WHEN pdr.parentid IS NULL AND csd.toacceptdate IS NOT NULL AND csr.completedate IS NULL THEN '待完成'  
            WHEN pdr.parentid IS NULL AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL THEN '待转入'
            WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转入' 
        END status,
        csp.stepplancreatedate,
        csd.senddate,
        csp.stepplanshipdate,jje.fullname jjplaner,
        CASE WHEN csp.containerstepplanid IS NOT NULL THEN '已排产' ELSE '未排产' END planstatus,
        rbe.fullname rbplaner,
        csp.plannedstartdate,
        csd.needcompletedate,
        csd.estimatedcompletedate,
        csr.completedate,
        csr.recivedate shipindate,
        pdr.createdate denydate,de.fullname denyer,pdr.reasonnote denyreason,
        CASE WHEN pdr.parentid IS NOT NULL THEN 0
            WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NULL THEN 0
            WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NOT NULL 
                    AND csr.completedate IS NULL 
                    THEN TRUNC(SYSDATE) - TRUNC(csd.needcompletedate) ELSE TRUNC(csr.completedate) - TRUNC(csd.needcompletedate)
        END delay,
        CASE WHEN csd.toacceptdate IS NOT NULL AND csp.plannedstartdate IS NOT NULL THEN TO_CHAR(TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate))
            ELSE '--' 
        END sendoutdelay,
        CASE WHEN csd.toacceptdate IS NOT NULL AND csp.plannedstartdate IS NOT NULL THEN TO_CHAR(TRUNC(csp.plannedstartdate) -  TRUNC(csd.toacceptdate))
            ELSE '--' 
        END presend,
        csp.plannedfinishdate
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --批次所属分厂
LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid --工序所属分厂
LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = sp.containershiprecordid
LEFT JOIN processdealrecord pdr ON pdr.parentid = sp.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4) --转工拒收  
LEFT JOIN employee e ON e.employeeid = csd.toacceptor --热表接收人
LEFT JOIN employee de ON de.employeeid = pdr.employeeid --转工拒收人
LEFT JOIN employee jje ON jje.employeeid = csp.stepplancreateemployeeid --机加排产人
LEFT JOIN employee rbe ON rbe.employeeid = csp.stepplanshipemployeeid --热表排产人
LEFT JOIN containerstepnoship csn ON csn.containerid = cci.containerid AND csn.workflowstepid = csp.workflowstepid AND csn.noship = 2 AND csn.isuse = 0
WHERE csp.plantype = 2 AND cci.containerstatus = 1 AND csn.containerstepnoshipid IS NULL";
        //承制分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND f2.factoryname = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["originalfactoryid"] != null && jObject["originalfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["originalfactoryid"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //热表接收人
        if (jObject["receiver"] != null && jObject["receiver"].ToString() != "")
        {
            sql += string.Format(" AND cr.ToAcceptor = '{0}'", jObject["receiver"].ToString());
        }

        //热表接收日期
        if (jObject["receivedate"] != null && jObject["receivedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["receivedate"].ToString());
            sql += string.Format(" AND csd.toacceptdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csd.toacceptdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
        }

        //转工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "转工拒收")
            {
                sql += string.Format(" AND pdr.parentid IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "未转出")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.senddate IS NULL ");
            }
            if (jObject["status"].ToString() == "待接收")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.senddate IS NOT NULL AND cr.toacceptdate IS NULL ");
            }
            if (jObject["status"].ToString() == "待完成")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.toacceptdate IS NOT NULL AND cr.completedate IS NULL ");
            }
            if (jObject["status"].ToString() == "待转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.completedate IS NOT NULL AND cr.recivedate IS NULL ");
            }
            if (jObject["status"].ToString() == "已转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.recivedate IS NOT NULL ");
            }
        }

        //计划发出时间
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        return sql;
    }


    // 计算排产率统计数据
    Result getComputeData(JObject jObject)
    {
        /**
         * 机加排产率： C/D
         * 分母D: 本周排产的数据
         * 分子C: 本周4这一天排产的，计划下周发出的数据
         */

        /**
         *排产临时项: D 排除 C 
         */

        /**
         * 准时到达率: A/B
         * B分母: 本周4这一天排产的，计划下周发出的数据
         * A分子：在B里边筛选 【发出超期时间】= 0 或 【发出超期时间】= -1。【发出超期时间】 = 【热表接收时间】 - 【机加计划发出时间】
         */

        DataTable dtResult = new DataTable();
        dtResult.Columns.Add("total");//总数
        dtResult.Columns.Add("valid");//周四排的下周发出的 (有效排产)
        dtResult.Columns.Add("invalid");//临时排产项 （无效排产）
        dtResult.Columns.Add("intime");//准时到达项
        dtResult.Columns.Add("notintime");//非准时到达项
        dtResult.Columns.Add("plan-rate");//排产率
        dtResult.Columns.Add("intime-rate");//准时到达率
        DataRow dr = dtResult.NewRow();

        string strPlanRateFMSql = getComputeDataSql(jObject);
        // 查询排产率计算公式中分母的数据
        DataTable dtPlanRateFM = dal.GetDataTable(strPlanRateFMSql);
        dr["total"] = dtPlanRateFM.Rows.Count;

        string strPlanRateFZSql = getComputeDataSql(jObject);
        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            if (arr.Count == 2)
            {
                strPlanRateFZSql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                strPlanRateFZSql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }
        //在周4这天排的产
        //if (jObject["thursday"] != null && jObject["thursday"].ToString() != "")
        //{
        //    strPlanRateFZSql += string.Format(" AND TRUNC(csp.stepplancreatedate) = TO_DATE('{0}','yyyy-mm-dd')", jObject["thursday"].ToString());
        //}
        // 查询排产率计算公式中分子的数据
        DataTable dtPlanRateFZ = dal.GetDataTable(strPlanRateFZSql);
        dr["valid"] = dtPlanRateFZ.Rows.Count;//有效排产
        dr["invalid"] = dtPlanRateFM.Rows.Count - dtPlanRateFZ.Rows.Count;//计算临时排产项 = 本周排产的 - 本周4这一天排的且计划发出时间是下周的

        // 计算 排产率
        if (dtPlanRateFM.Rows.Count > 0)
        {
            decimal finishRate = ((decimal)dtPlanRateFZ.Rows.Count / dtPlanRateFM.Rows.Count) * 100;
            dr["plan-rate"] = finishRate.ToString("0.00");
        }
        else
        {
            dr["plan-rate"] = "--";
        }

        //计算 准时到达率
        if (dtPlanRateFZ.Rows.Count > 0)
        {
            DataRow[] drs = dtPlanRateFZ.Select(" sendoutdelay = '0' OR sendoutdelay = '-1' ");
            dr["intime"] = drs.Length;//准时到达项
            dr["notintime"] = dtPlanRateFZ.Rows.Count - drs.Length; //非准时到达项
            decimal finishRate = ((decimal)drs.Length / dtPlanRateFZ.Rows.Count) * 100;
            dr["intime-rate"] = finishRate.ToString("0.00");
        }
        else
        {
            dr["intime-rate"] = "--";
        }

        dtResult.Rows.Add(dr);

        Result r = new Result();

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtResult;
        r.total = 1;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getChart(JObject jObject)
    {
        Result r = new Result();
        string sql = getComputeDataSql(jObject);
        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }
        // 查询排产率计算公式中分子的数据
        DataTable dt = dal.GetDataTable(sql);

        if (dt.Rows.Count == 0)
        {
            r.data = null;
            r.total = 0;
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }

        DataTable dtResult = dt.DefaultView.ToTable(true, "factoryname");
        dtResult.Columns.Add("valid");//有效排产
        dtResult.Columns.Add("intime");//准时到达项
        dtResult.Columns.Add("notintime");//非准时到达项
        dtResult.Columns.Add("intimerate");//准时到达率

        foreach (DataRow row in dtResult.Rows)
        {
            int valid = dt.Select(string.Format("factoryname='{0}'", row["factoryname"])).Length;
            row["valid"] = valid;
            DataRow[] drs = dt.Select(string.Format("factoryname='{0}' AND (sendoutdelay = '0' OR sendoutdelay = '-1')", row["factoryname"]));
            row["intime"] = drs.Length;//准时到达项
            row["notintime"] = valid - drs.Length; //非准时到达项
            decimal finishRate = ((decimal)drs.Length / valid) * 100;
            row["intimerate"] = finishRate.ToString("0.00");//准时到达率
        }

        DataRow dr = dtResult.NewRow();
        dr["factoryname"] = "全部";
        dr["valid"] = dt.Rows.Count;//有效排产
        //计算 准时到达率
        if (dt.Rows.Count > 0)
        {
            DataRow[] drs = dt.Select(" sendoutdelay = '0' OR sendoutdelay = '-1' ");
            dr["intime"] = drs.Length;//准时到达项
            dr["notintime"] = dt.Rows.Count - drs.Length; //非准时到达项
            decimal finishRate = ((decimal)drs.Length / dt.Rows.Count) * 100;
            dr["intimerate"] = finishRate.ToString("0.00");//准时到达率
        }
        else
        {
            dr["intimerate"] = "--";
        }

        dtResult.Rows.Add(dr);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtResult;
        r.total = dtResult.Rows.Count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getChartDetail(JObject jObject)
    {
        string sql = getComputeDataSql(jObject);
        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }
        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["seriesName"] != null)
        {
            if (jObject["seriesName"].ToString() == "准时到达项")
            {
                sql += " AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)";
            }
            if (jObject["seriesName"].ToString() == "未准时到达项")
            {
                sql += @" 
AND (csd.toacceptdate IS NULL 
     OR (csd.toacceptdate IS NOT NULL 
         AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) <> 0 
         AND TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) <> -1)
        )
    )";
            }
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }
            if (dr["presend"].ToString() != "--" && Convert.ToDouble(dr["presend"].ToString()) < 0)
            {
                dr["presend"] = 0;
            }
            if (dr["sendoutdelay"].ToString() != "--" && Convert.ToDouble(dr["sendoutdelay"].ToString()) < 0)
            {
                dr["sendoutdelay"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询导出公用sql
    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT  DISTINCT t.*,
        CASE WHEN t.sendoutdelay = '--' THEN '--'
             WHEN t.sendoutdelay <> '--' AND t.recivedate IS NOT NULL AND TO_NUMBER(t.presend) > 0 THEN NULL
             WHEN t.sendoutdelay <> '--' AND t.recivedate IS NOT NULL AND TO_NUMBER(t.presend) <= 0 THEN t.sendoutdelay
        END fccq,--发出超期时间
        CASE WHEN t.presend = '--' THEN '--'
             WHEN t.presend <> '--' AND t.recivedate IS NOT NULL AND TO_NUMBER(t.sendoutdelay) > 0 THEN NULL
             WHEN t.presend <> '--' AND t.recivedate IS NOT NULL AND TO_NUMBER(t.sendoutdelay) <= 0 THEN t.presend
        END fctq --发出提前时间
FROM (
        SELECT DISTINCT f.factoryname,cci.fromworkcentername,cci.productname,cci.productdescription,cci.containername,cci.workflowstepname currentspec,
               cci2.containername rbcontainername,
               CASE WHEN csr.recivedate IS NOT NULL THEN (csr.acceptanceqty + csr.lossqty) ELSE cci.qty END qty,
               ws.workflowstepname,
               e.fullname receiver,csd.toacceptdate recivedate,
               CASE WHEN pdr.parentid IS NOT NULL THEN '转工拒收'
                    WHEN pdr.parentid IS NULL AND csd.senddate IS NULL THEN '未转出'
                    WHEN pdr.parentid IS NULL AND csd.senddate IS NOT NULL AND csd.toacceptdate IS NULL THEN '待接收'  
                    WHEN pdr.parentid IS NULL AND csd.toacceptdate IS NOT NULL AND csr.completedate IS NULL THEN '待完成'  
                    WHEN pdr.parentid IS NULL AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL THEN '待转入'
                    WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转入' 
               END status,
               csp.stepplancreatedate,
               csd.senddate,
               csp.stepplanshipdate,jje.fullname jjplaner,
               CASE WHEN csp.containerstepplanid IS NOT NULL THEN '已排产' ELSE '未排产' END planstatus,
               rbe.fullname rbplaner,
               csp.plannedstartdate,
               csd.needcompletedate,
               csd.estimatedcompletedate,
               csr.completedate,
               csr.recivedate shipindate,
               pdr.createdate denydate,de.fullname denyer,pdr.reasonnote denyreason,
               CASE WHEN pdr.parentid IS NOT NULL THEN 0
                    WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NULL THEN 0
                    WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NOT NULL 
                         AND csr.completedate IS NULL 
                         THEN TRUNC(SYSDATE) - TRUNC(csd.needcompletedate) ELSE TRUNC(csr.completedate) - TRUNC(csd.needcompletedate)
               END delay,
               CASE WHEN csd.toacceptdate IS NOT NULL AND csp.plannedstartdate IS NOT NULL THEN TO_CHAR(TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate))
                    ELSE '--' 
               END sendoutdelay,
               CASE WHEN csd.toacceptdate IS NOT NULL AND csp.plannedstartdate IS NOT NULL THEN TO_CHAR(TRUNC(csp.plannedstartdate) -  TRUNC(csd.toacceptdate))
                    ELSE '--' 
               END presend,
               csp.plannedfinishdate,
               cci.workflowname,cci.workflowrevision,wfb.workflowname pworkflowname,wf.workflowrevision pworkflowrevision,
               CASE WHEN csd2.containerstepdispatchid IS NOT NULL THEN '已派工' ELSE '未派工' END dispatchstatus
        FROM containerstepplan csp
        LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
        LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --批次所属分厂
        LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid --工序所属分厂
        LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
        LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid
        LEFT JOIN containershiprecive csr ON csr.containershiprecordid = sp.containershiprecordid
        LEFT JOIN processdealrecord pdr ON pdr.parentid = sp.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4) --转工拒收  
        LEFT JOIN employee e ON e.employeeid = csd.toacceptor --热表接收人
        LEFT JOIN employee de ON de.employeeid = pdr.employeeid --转工拒收人
        LEFT JOIN employee jje ON jje.employeeid = csp.stepplancreateemployeeid --机加排产人
        LEFT JOIN employee rbe ON rbe.employeeid = csp.stepplanshipemployeeid --热表排产人
        LEFT JOIN containerstepnoship csn ON csn.containerid = cci.containerid AND csn.workflowstepid = csp.workflowstepid AND csn.noship = 2 AND csn.isuse = 0
        LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
        LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
        LEFT JOIN containersubcontainer sub ON sub.containerid = csp.containerid AND sub.workflowstepid = csp.workflowstepid --关联热表子卡
        LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.subcontainerid
        LEFT JOIN containerstepdispatch2 csd2 ON csd2.containerid = cci2.containerid AND csd2.isuse = 0
        WHERE csp.plantype = 2 AND cci.containerstatus = 1 AND csn.containerstepnoshipid IS NULL";

        //工序所属分厂
        if (jObject["tofactoryname"] != null && jObject["tofactoryname"].ToString() != "")
        {
            sql += string.Format(" AND f2.factoryname = '{0}'", jObject["tofactoryname"].ToString());
        }

        //主制分厂
        if (jObject["originalfactoryid"] != null && jObject["originalfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["originalfactoryid"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //接收人（调度员）
        if (jObject["receiver"] != null && jObject["receiver"].ToString() != "")
        {
            sql += string.Format(" AND cr.toacceptor = '{0}'", jObject["receiver"].ToString());
        }

        //接收日期
        if (jObject["receivedate"] != null && jObject["receivedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["receivedate"].ToString());
            sql += string.Format(" AND cr.toacceptdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND cr.toacceptdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
        }

        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        //转工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "转工拒收")
            {
                sql += string.Format(" AND pdr.parentid IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "未转出")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csd.senddate IS NULL ");
            }
            if (jObject["status"].ToString() == "待接收")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csd.senddate IS NOT NULL AND csd.toacceptdate IS NULL ");
            }
            if (jObject["status"].ToString() == "待完成")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csd.toacceptdate IS NOT NULL AND csr.completedate IS NULL ");
            }
            if (jObject["status"].ToString() == "待转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL ");
            }
            if (jObject["status"].ToString() == "已转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.recivedate IS NOT NULL ");
            }
        }
        sql += " ) t ";

        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }
            if (dr["presend"].ToString() != "--" && Convert.ToDouble(dr["presend"].ToString()) < 0)
            {
                dr["presend"] = 0;
            }
            if (dr["sendoutdelay"].ToString() != "--" && Convert.ToDouble(dr["sendoutdelay"].ToString()) < 0)
            {
                dr["sendoutdelay"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }
        }

        dtResult.Columns.Remove("sendoutdelay");
        dtResult.Columns.Remove("presend");

        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "转工工序";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["recivedate"].Caption = "接收时间";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["stepplancreatedate"].Caption = "机加排产时间";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["stepplanshipdate"].Caption = "热表排产时间";
        dtResult.Columns["jjplaner"].Caption = "机加排产人";
        dtResult.Columns["planstatus"].Caption = "排产状态";
        dtResult.Columns["rbplaner"].Caption = "承制分厂排产人";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["shipindate"].Caption = "转入时间";
        dtResult.Columns["denydate"].Caption = "拒收时间";
        dtResult.Columns["denyer"].Caption = "拒收人";
        dtResult.Columns["denyreason"].Caption = "拒收原因";
        dtResult.Columns["delay"].Caption = "完成超期时间";
        dtResult.Columns["fccq"].Caption = "发出超期时间";
        dtResult.Columns["fctq"].Caption = "发出提前时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["workflowname"].Caption = "当前在制工艺路线";
        dtResult.Columns["workflowrevision"].Caption = "当前在制工艺版本";
        dtResult.Columns["pworkflowname"].Caption = "排产工艺路线";
        dtResult.Columns["pworkflowrevision"].Caption = "排产工艺版本";
        dtResult.Columns["rbcontainername"].Caption = "热表批次号";
        dtResult.Columns["dispatchstatus"].Caption = "热表是否派工";
        ExportHelper.ExportExcel(dtResult, "热表分厂转工报表");
    }

    //导出前处理
    DataTable setDataColumn(DataTable dtResult)
    {
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }

            if (dr["sendoutdelay"].ToString() != "--" && Convert.ToDouble(dr["sendoutdelay"].ToString()) < 0)
            {
                dr["sendoutdelay"] = 0;
            }

            if (dr["presend"].ToString() != "--" && Convert.ToDouble(dr["presend"].ToString()) < 0)
            {
                dr["presend"] = 0;
            }
        }

        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "转工工序";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["recivedate"].Caption = "接收时间";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["stepplancreatedate"].Caption = "机加排产时间";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["stepplanshipdate"].Caption = "热表排产时间";
        dtResult.Columns["jjplaner"].Caption = "机加排产人";
        dtResult.Columns["planstatus"].Caption = "排产状态";
        dtResult.Columns["rbplaner"].Caption = "承制分厂排产人";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["shipindate"].Caption = "转入时间";
        dtResult.Columns["denydate"].Caption = "拒收时间";
        dtResult.Columns["denyer"].Caption = "拒收人";
        dtResult.Columns["denyreason"].Caption = "拒收原因";
        dtResult.Columns["delay"].Caption = "完成超期时间";
        dtResult.Columns["sendoutdelay"].Caption = "发出超期时间";
        dtResult.Columns["presend"].Caption = "发出提前时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        return dtResult;
    }

    //导出排产率明细
    void exportPlanRate(JObject jObject)
    {
        string sql = getComputeDataSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setDataColumn(dtResult);
        ExportHelper.ExportExcel(dtResult, "排产率明细");
    }

    //导出排产临时项
    void exportPlanTemp(JObject jObject)
    {
        string sql = getComputeDataSql(jObject);
        sql += " AND NOT (";
        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        sql += " ) ";
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setDataColumn(dtResult);
        ExportHelper.ExportExcel(dtResult, "排产临时项明细");
    }

    //导出未准时到达项
    void exportNoIntime(JObject jObject)
    {
        string sql = getComputeDataSql(jObject);
        //计划发出日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        sql = string.Format("SELECT t.* FROM ({0}) t WHERE t.sendoutdelay <> '0' AND t.sendoutdelay <> '-1'", sql);

        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setDataColumn(dtResult);
        ExportHelper.ExportExcel(dtResult, "未准时到达明细");
    }
}